"""
自定义副表类
"""
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import json

engine = create_engine(url='mysql+pymysql://root:123!#Qw@localhost/company?charset=utf8', echo=False)
session = sessionmaker(bind=engine)()
Base = declarative_base()


class Record(Base):
    __tablename__ = 'record'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    server_id = Column(Integer, ForeignKey('server.id'))


class Server(Base):
    __tablename__ = 'server'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False)
    ip = Column(String(64), nullable=False)

    def __repr__(self):
        return f'名称：{self.name}  ip:{self.ip}'


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False)
    # server = relationship('Server', secondary='record', backref='user')
    server = relationship('Server', secondary=Record.__table__, backref='user')

    def __repr__(self):
        return f'用户名：{self.name}  网站:{self.server}'


Base.metadata.create_all(engine)
u1 = User(name='张三')
u2 = User(name='李四')
s1 = Server(name='Python', ip='192.168.2.1')
s2 = Server(name='B站', ip='192.168.2.2')
u1.server = [s1, s2]
u2.server = [s2]

# session.add_all([u1, u2, s1, s2])  #可以不写改行，也可以生成数据

session.commit()

res = session.query(User).filter(User.name == "张三").all()
print(res)
res = session.query(User).filter(User.name == "李四").all()
print(res)
